<?php

mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_customer = "SELECT customer.id as customer_id, person.first_name, person.last_name FROM customer inner join person on (customer.person_id=person.id) order by person.last_name ASC ";
$customer = mysql_query($query_customer, $drycleaners_conn) or die(mysql_error());
$row_customer = mysql_fetch_assoc($customer);
$totalRows_customer = mysql_num_rows($customer);

get_main_menu($_REQUEST['menu_type']) ?>
   <br/>
   <div style="width:90%; margin-left:auto; margin-right:auto;">
    <div class="form_title">

            <?php echo $_REQUEST["action"];?>Customer Balance Summary Generator <?php echo $row_get_branch_by_id['name']?>
    </div>
    <div class="box"> 
<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
  <table align="left">
         <tr valign="middle">
      <td height="32" align="left" nowrap="nowrap">From Date</td>
      <td align="center"><script>DateInput('from_date', true, 'YYYY-MON-DD')</script></td>

      <td align="left" nowrap="nowrap">To Date</td>
      <td><script>DateInput('to_date', true, 'YYYY-MON-DD')</script></td>
      <td align="left" nowrap="nowrap">&nbsp;</td>
      <td valign="top">
      
		  <input name='create_report' type='submit' value='Search' id='submit' style="width:100px; height:30px; margin-top:0px; padding-top:5px;"/>
          <?php if(isset($_REQUEST['create_report'])){?>
          <a href="?customers_summary_exporter&from_date=<?php echo $_REQUEST['from_date'] ?>&to_date=<?php echo $_REQUEST['to_date'] ?>&export=true" target="_blank"><input name='create_report' type='button' value='Export' id='submit' style="width:100px; height:30px; margin-top:0px;padding-top:5px;"/></a>
          
          <a href="?customers_summary_exporter&from_date=<?php echo $_REQUEST['from_date'] ?>&to_date=<?php echo $_REQUEST['to_date'] ?>" target="_blank"><input name='create_report' type='button' value='Print' id='submit' style="width:100px; height:30px; margin-top:0px;padding-top:5px;"/></a>
          <?php }?>
		   </td>
    </tr>
  </table>
  <input type="hidden" name="record_status" value="active" />
  <input type="hidden" name="MM_insert" value="report_form" />
</form>

</div>
<div>
<?php
if(isset($_REQUEST['create_report'])){	
?>
<?php include"includes/table_settings.php"?>
<table  cellpadding="0" cellspacing="0" border="0" id="dataTable">
   <thead>
     <tr>
        <th align="left">Transaction/Customer</th>
        <th align="left">Amount</th>      
    </tr>
    </thead>
<?php
	do{
		?>
    <tr>
        <th align="left"><?php echo $row_customer['first_name']." ".$row_customer['last_name']." Id:".$row_customer['customer_id'] ?></th>
  <?php
		
	mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order = "SELECT sum(order_payment.amount) as total,
       `order`.id,
       person.first_name,
       person.last_name,
	   order.created_date,
       `order`.order_no
  FROM    (   (   drycleaners.customer customer
               INNER JOIN
                  drycleaners.person person
               ON (customer.person_id = person.id))
           INNER JOIN
              drycleaners.`order` `order`
           ON (`order`.customer_id = customer.id))
       LEFT OUTER JOIN
          drycleaners.order_payment order_payment
       ON (order_payment.order_id = `order`.id)
 WHERE  order.created_date BETWEEN '".$_REQUEST['from_date']." 00:00:00"."' AND '".$_REQUEST['to_date']." 23:00:00"."'  AND order.customer_id='".$row_customer['customer_id']."'
 Group By `order`.id
 ";
$order = mysql_query($query_order, $drycleaners_conn) or die(mysql_error());
$row_order = mysql_fetch_assoc($order);
$totalRows_order = mysql_num_rows($order);



mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order_payments = "SELECT order_payment.amount,
       `order`.id,
	   order_payment.id as payment_id,
       `order`.created_date, order.order_no,
       order_payment.created_date as payment_date
  FROM    drycleaners.order_payment order_payment
        INNER JOIN
          drycleaners.`order` `order`
       ON (order_payment.order_id = `order`.id)
 WHERE order.customer_id='".$row_customer['customer_id']."' AND order.created_date BETWEEN '".$_REQUEST['from_date']." 00:00:00"."' AND '".$_REQUEST['to_date']." 23:00:00"."' 
 ";
$order_payments = mysql_query($query_order_payments, $drycleaners_conn) or die(mysql_error());
$row_order_payments = mysql_fetch_assoc($order_payments);
$totalRows_order_payments = mysql_num_rows($order_payments);
?>

<?php
$branch_total_bill=0;
do{
?>

<?php $total_bill=0;?>

<?php
mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order_item_by_order_id = sprintf("SELECT material.name as material_name,
       item.name as item_name,
       type_of_service.name as type_of_service_name,
       order_item.color,
	   order_item.id,
	   order_item.material_id,
	   order_item.item_id
  FROM    (   (   drycleaners.order_item order_item
               INNER JOIN
                  drycleaners.type_of_service type_of_service
               ON (order_item.type_of_service_id = type_of_service.id))
           INNER JOIN
              drycleaners.item item
           ON (order_item.item_id = item.id))
       INNER JOIN
          drycleaners.material material
       ON (order_item.material_id = material.id)
	   WHERE
	    order_id =%s", GetSQLValueString($row_order['id'], "int"));
$order_item_by_order_id = mysql_query($query_order_item_by_order_id, $drycleaners_conn) or die(mysql_error());
$row_order_item_by_order_id = mysql_fetch_assoc($order_item_by_order_id);

do{
	$cost=0;
	
	mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order_item_service = "SELECT order_item_service.id, service.name as service_name,order_item_service.service_id
  FROM    drycleaners.order_item_service order_item_service
       INNER JOIN
          drycleaners.service service
       ON (order_item_service.service_id = service.id) WHERE order_item_id='".$row_order_item_by_order_id['id']."'";
	   
$order_item_service = mysql_query($query_order_item_service, $drycleaners_conn) or die(mysql_error());
$row_order_item_service = mysql_fetch_assoc($order_item_service);
	

	do{
		
		$query_prices="SELECT material_service_cost.price, material_service_cost.express_extra_price
  FROM drycleaners.material_service_cost material_service_cost Where material_id='".$row_order_item_by_order_id['material_id']."' AND item_id='".$row_order_item_by_order_id['item_id']."' AND service_id='".$row_order_item_service['service_id']."'";

  $get_prices=mysql_query($query_prices,$drycleaners_conn) or die(mysql_error());
  $row_get_prices = mysql_fetch_assoc($get_prices);
	   
	   if($row_order_item_by_order_id["name"]='express'){
	   $service_cost=$row_get_prices['price']+$row_get_prices['express_extra_price'];
	   }
	   else{
		   $service_cost=$row_get_prices['price'];
		   }
		   
		   $cost=$cost+$service_cost;
		}while($row_order_item_service=mysql_fetch_assoc($order_item_service));
	$total_bill=$total_bill+$cost;
	}while($row_order_item_by_order_id=mysql_fetch_assoc($order_item_by_order_id));
?>
<?php
mysql_free_result($order_item_by_order_id);

mysql_free_result($order_item_service);
?>
<?php
$branch_total_bill=$branch_total_bill+$total_bill;
 }while($row_order=mysql_fetch_assoc($order));
 
 $total_payment=0;
?>

    <?php if($totalRows_order_payments>0){
		
		do{
	?>
<?php 
$total_payment=$total_payment+$row_order_payments['amount'];
} while($row_order_payments=mysql_fetch_assoc($order_payments));
	}
?>
        <th align="left"> <?php echo $branch_total_bill-$total_payment;?></th>
    </tr>

<?php
}while($row_customer=mysql_fetch_assoc($customer));
}

?>
</table>
    <?php
mysql_free_result($customer);
?>
</div>
